package com.wangshuqi.jdbc.preparedStatement;

import com.wangshuqi.jdbc.util.JDBCUtil;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;

/**
 * 使用preparedStatement进行增删改操作
 *
 * @author codewsq
 * @date 2021/8/21 0021 17:07
 */
public class PreparedStatementUpdateTest {

    /*
     * 给customers表新增一条数据（完整流程）
     * */
    @Test
    public void test1() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 1.加载配置文件
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(is);
            // 2. 读取配置信息
            String driver = properties.getProperty("driver");
            String url = properties.getProperty("url");
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            // 3. 加载驱动
            Class.forName(driver);
            // 4. 获取数据库连接
            connection = DriverManager.getConnection(url, user, password);
            // 5.编写sql
            String sql = "insert into customers(name,email,birth) values (?, ?, ?)";
            // 6. 预编译sql
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "哪吒"); // 数据库从1开始
            preparedStatement.setString(2, "nezha@163.com");
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            preparedStatement.setDate(3, new Date(simpleDateFormat.parse("1010-12-01").getTime()));
            // 7.执行sql语句
            preparedStatement.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        } finally {
            // 8.关闭连接
            try {
                if (preparedStatement != null)
                    preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 封装增删改操作，然后调用使用
    @Test
    public void test2() {
        // 更新id为22的客户名称为孙悟空
        String sql = "update customers set name = ? where id = ?";
        String name = "哪吒";
        Integer id = 22;
        int count = JDBCUtil.saveOrUpdateOrDelete(sql, name, id);
        if (count > 0) {
            System.out.println("更新成功：" + count);
        } else {
            System.out.println("没有找到对应数据：" + count);
        }

        // 根据id删除指定用户
        sql = "delete from `customers` where id = ?";
        int result = JDBCUtil.saveOrUpdateOrDelete(sql, 3);
        if (result > 0) {
            System.out.println("删除成功：" + result);
        } else {
            System.out.println("没有找到对应数据：" + result);
        }
    }
}
